﻿using OfficeOpenXml; 
using System; 
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text; 
using UnityEditor;
using UnityEngine;
using Newtonsoft.Json;

namespace XFGameFramework.ExcelSystem
{
    /// <summary>
    /// ExcelSystem业务逻辑
    /// </summary>
    public class ExcelManager 
    {

        /// <summary>
        /// Excel文件最大的行数
        /// </summary>
        private const int MAX_ROW = 1048576;

        /// <summary>
        /// Excel文件最大的列数
        /// </summary>
        private const int MAX_COL = 16384;

        /// <summary>
        /// 数据类型
        /// </summary>
        private const string DATA_TYPE = "DATA_TYPE_FULL_CLASS_NAME";

        /// <summary>
        /// 创建 Excel(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <param name="path"></param>
        public static void CreateExcel(string path)
        {
            // 如果文件已经存在 直接return 即可
            if (File.Exists(path)) return;

            FileInfo fileInfo = new FileInfo(path);
            using ExcelPackage package = new ExcelPackage(fileInfo); 
            package.Workbook.Worksheets.Add("Sheet1"); 
            package.Save(); 
        }

        /// <summary>
        /// 把excel转换成json(excel 前三行必须分别是(字段名 类型 描述) 从第四行开始才是具体的数据 ) 
        /// *注:仅支持.xlsx
        /// (仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <param name="excel_path">excel文件路径</param>
        /// <param name="json_path">转换后json的保存路径</param>
        /// <param name="exclude_prefix">排除字段的前缀</param>
        public static void ExcelToJson(string excel_path, string json_path, string exclude_prefix = "")
        {
            if (excel_path.EndsWith(".xls"))
            {
                Debug.LogError("仅支持.xlsx");
                return;
            }

            if (!File.Exists(excel_path))
            {
                Debug.LogErrorFormat("未查询到配表:{0}", excel_path);
                return;
            }

            if (string.IsNullOrEmpty(json_path))
            {
                Debug.LogError("json_path不能为空!");
                return;
            }

            using ExcelPackage excel = new ExcelPackage(new FileInfo(excel_path));
             
            ExcelWorksheet sheet = excel.Workbook.Worksheets[1];

            int col = GetExcelCol(sheet);

            int row = GetExcelRow(sheet);

            if (row < 3)
            {
                Debug.LogErrorFormat("配表:{0}格式错误,行数:{1}小于3!", excel_path, row);
                return;
            }

            // 行数满足 读取所有的字段  key:列 value:字段 
            Dictionary<int, ExcelFields> fields = ParseFields(sheet, col, excel_path,exclude_prefix);

            if (fields.Count == 0)
            {
                Debug.LogWarningFormat("未在配表:{0}中查询到字段,请检查配表格式是否正确!",excel_path);
                return;
            }

            // 所有的数据
            List<Dictionary<string, object>> datas = new List<Dictionary<string, object>>();

            for (int i = 4; i <= row; i++)
            {
                Dictionary<string, object> item = new Dictionary<string, object>();

                foreach (var column in fields.Keys)
                {
                    object obj = sheet.GetValue(i, column);
                    obj = ParseFiledData(obj, fields[column].type);
                    item.Add(fields[column].name, obj);
                }

                datas.Add(item);
            }
             
            System.IO.File.WriteAllText(json_path, JsonConvert.SerializeObject(datas, Formatting.Indented),Encoding.UTF8);
        }

        /// <summary>
        /// 把某个目录下的所有excel文件转换成json(excel 前三行必须分别是(字段名 类型 描述) 从第四行开始才是具体的数据 )
        /// *注:仅支持.xlsx
        /// (仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <param name="excel_dir">excel文件目录</param> 
        /// <param name="json_dir">转换后json的目录</param>
        /// <param name="exclude_prefix">排除字段的前缀</param>
        public static void ExcelDirToJson(string excel_dir , string json_dir , string exclude_prefix = "") 
        { 
            // 目录是否存在 
            if (!Directory.Exists(excel_dir)) 
            {
                Debug.LogErrorFormat("excel目录:{0}不存在!",excel_dir);
                return;
            }

            // 判断目标目录是否为空 
            if (string.IsNullOrEmpty(json_dir))
            {
                Debug.LogError("json目录不能为空!");
                return;
            }

            // 获取到目录下的所有表格文件 TODO 

            List<string> files = new List<string>();

            files.AddRange( Directory.GetFiles(excel_dir, "*.xlsx", SearchOption.AllDirectories)); 
              
            if (files.Count == 0) 
            {
                Debug.LogWarningFormat("目录:{0}中未查询到excel表格文件!",excel_dir);
                return;
            }

            if (!Directory.Exists(json_dir))  
                Directory.CreateDirectory(json_dir);
             
            try
            {
                for (int i = 0; i < files.Count; i++)
                {
                    string json_path = string.Format("{0}/{1}.json", json_dir, Path.GetFileNameWithoutExtension(files[i]));
                    ExcelToJson(files[i], json_path, exclude_prefix);

                    EditorUtility.DisplayProgressBar("正在转换Excel...", files[i], (i + 1) * 1.0f / files.Count);
                }
            }
            catch (Exception)
            {
                 
            }
            finally 
            {
                // 清空进度
                EditorUtility.ClearProgressBar();
            } 

        }

        /// <summary>
        /// 把Excel转成ScriptableObject集合(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="excel_path">excel路径</param>
        /// <param name="scriptables">ScriptableObject集合</param>
        /// <param name="config">配置文件</param>
        /// <param name="error">报错信息</param>
        /// <param name="generate_path">生成的ScriptableObject路径</param>
        /// <returns></returns>
        public static bool ExcelToScriptableObject<T>(string excel_path,List<T> scriptables, ScriptableObject config,out string error,string generate_path = null) where T : ScriptableObject
        { 

            if (excel_path.EndsWith(".xls"))
            {
                error = "仅支持.xlsx";
                Debug.LogError(error);
                return false;
            }

            if (!File.Exists(excel_path))
            {
                error = string.Format("未查询到配表:{0}", excel_path);
                Debug.LogError(error);
                return false;
            }
             
            using ExcelPackage excel = new ExcelPackage(new FileInfo(excel_path));

            ExcelWorksheet sheet = excel.Workbook.Worksheets[1];

            int col = GetExcelCol(sheet);

            int row = GetExcelRow(sheet);

            if (row < 3)
            {
                error = string.Format("配表:{0}格式错误,行数:{1}小于3!", excel_path, row);
                Debug.LogError(error);
                return false;
            }

            // 行数满足 读取所有的字段  key:列 value:字段 
            Dictionary<int, ExcelFields> fields = ParseFields(sheet, col, excel_path,string.Empty);

            Dictionary<string, ExcelFields> fields_key_name = new Dictionary<string, ExcelFields>();

            foreach (var item in fields.Values)
            {
                fields_key_name.Add(item.name, item);
            }


            if (fields.Count == 0)
            {
                error = string.Format("未在配表:{0}中查询到字段,请检查配表格式是否正确!", excel_path);
                Debug.LogWarning(error);
                return false;
            }

            // 所有的数据
            List<Dictionary<string, object>> datas = new List<Dictionary<string, object>>();

            for (int i = 4; i <= row; i++)
            {
                Dictionary<string, object> item = new Dictionary<string, object>();

                foreach (var column in fields.Keys)
                {
                    object obj = sheet.GetValue(i, column);
                    item.Add(fields[column].name, obj);
                }

                datas.Add(item);
            }

            Dictionary<string,ScriptableObject> scriptables_dic = new Dictionary<string,ScriptableObject>();

            // 过滤掉空的数据
            for (int i = scriptables.Count - 1; i >= 0; i--)
            {
                if (scriptables[i] == null)
                    scriptables.RemoveAt(i);
            }

            foreach (var scriptable in scriptables) 
            {
                Type type = scriptable.GetType();

                // 找到 id 属性
                SerializedObject serializedObject = new SerializedObject(scriptable);
                List<SerializedProperty> properties = serializedObject.GetAllSerializedProperty();
                foreach (var item in properties)
                {
                    FieldInfo fieldInfo = type.GetField(item.name, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance); 
                    if (fieldInfo == null) continue;  
                    object idObj = fieldInfo.GetValue(scriptable);
                    if (idObj == null) continue;
                    
                    if (fieldInfo.GetCustomAttribute<ExcelScriptableObjectID>() != null || item.name.ToLower() == "id") 
                    {   
                        string id = idObj.ToString();

                        if (scriptables_dic.ContainsKey(id)) 
                        { 
                            Debug.LogWarningFormat("配置:{0} id:{1} 重复!",AssetDatabase.GetAssetPath(scriptable),id);
                            continue;
                        } 
                        scriptables_dic.Add(id, scriptable);
                        break;
                    }
                }
            }
              
            Dictionary<string, ScriptableObject> currentDatas = new Dictionary<string, ScriptableObject>();

            // 遍历数据

            try
            {
                for (int i = 0; i < datas.Count; i++)
                {
                    EditorUtility.DisplayProgressBar("正在导入配置...", excel_path, (i + 1) * 1.0f / datas.Count);

                    Dictionary<string, object> item = datas[i];
                    if (!item.ContainsKey(DATA_TYPE))
                    {
                        Debug.LogWarningFormat("未查询到类型字段:{0}", DATA_TYPE);
                        continue;
                    }
                    // 先找到类型
                    Type type = AssemblyTools.GetType(item[DATA_TYPE].ToString());
                    if (type == null)
                    {
                        Debug.LogWarningFormat("无效的类型:{0}", item[DATA_TYPE]);
                        continue;
                    }

                    if (type != typeof(T) && !type.IsSubclassOf(typeof(T))) 
                    {
                        Debug.LogWarningFormat("Excel中类型:{0}与目标类型不匹配:{1}", item[DATA_TYPE],typeof(T).FullName);
                        continue;
                    }

                    string id = null;

                    FieldInfo[] fieldInfos = type.GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                    foreach (FieldInfo field in fieldInfos)
                    {
                        ExcelScriptableObjectID excelScriptableObjectID = field.GetCustomAttribute<ExcelScriptableObjectID>();
                        if (excelScriptableObjectID == null) continue;
                        if (item.ContainsKey(field.Name))
                            id = item[field.Name].ToString();
                        break;
                    }


                    if (id == null) 
                    { 
                        foreach (var key in item.Keys)
                        {
                            if (key.ToLower() == "id")
                            {
                                id = item[key].ToString();
                                break;
                            }
                        }

                        if (id == null)
                        {
                            Debug.LogWarning("未查询到id字段!");
                            continue;
                        }
                    }
                     
                    // 过滤掉id重复的数据
                    if (currentDatas.ContainsKey(id))
                        continue;

                    ScriptableObject target = null;

                    if (scriptables_dic.ContainsKey(id))
                    {
                        if (scriptables_dic[id].GetType() == type)
                            target = scriptables_dic[id];
                    }

                    if (target == null)
                    {
                        target = ScriptableObject.CreateInstance(type);

                        string path = null;

                        if (string.IsNullOrEmpty(generate_path))
                        {
                            string asset_path = AssetDatabase.GetAssetPath(config);
                            if (string.IsNullOrEmpty(asset_path))
                            {
                                error = "未查询到配表所在路径!";
                                return false;
                            }
                            path = string.Format("{0}/{1}/{2}.asset", Path.GetDirectoryName(asset_path), Path.GetFileNameWithoutExtension(asset_path), id);
                        }
                        else 
                        {
                            // 用传入的路径
                            path = string.Format("{0}/{1}.asset", generate_path, id);
                        }
                         

                        if (!Directory.Exists(Path.GetDirectoryName(path)))
                            Directory.CreateDirectory(Path.GetDirectoryName(path));
                        AssetDatabase.CreateAsset(target, path);
                        AssetDatabase.Refresh();
                    }

                    // 把 item 数据 设置给 ScriptableObject 
                    foreach (var fieldName in item.Keys)
                    {
                        if (fieldName == DATA_TYPE) continue;

                        FieldInfo fieldInfo = type.GetField(fieldName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);
                         
                        object obj = ParseFiledData(item[fieldName], fields_key_name[fieldName].type, true, fieldInfo, target);

                        SetFieldData(target,fieldInfo, obj, fields_key_name[fieldName].type);
                    }

                    EditorUtility.SetDirty(target);
                    AssetDatabase.SaveAssets();
                    currentDatas.Add(id, target);
                    AssetDatabase.Refresh(); // 刷新数据
                }
            }
            catch (Exception e)
            {
                Debug.LogException(e);
            }
            finally 
            {
                EditorUtility.ClearProgressBar();
            }

             
            // 同步数据
            for (int i = 0; i < scriptables.Count; i++)
            {
                ScriptableObject d = scriptables[i];
                if (!currentDatas.ContainsValue(d))
                {
                    if (string.IsNullOrEmpty(AssetDatabase.GetAssetPath(d)))
                        continue; 
                    AssetDatabase.DeleteAsset(AssetDatabase.GetAssetPath(d));
                }
            }

            scriptables.Clear();

            foreach (var item in currentDatas.Values)
            {
                scriptables.Add(item as T);
            }
            
            SerializedObject configSerialized = new SerializedObject(config);
            List<SerializedProperty> serializedProperties = configSerialized.GetAllSerializedProperty();

            foreach (var serializedProperty in serializedProperties)
            {
                if(serializedProperty.name.ToLower() != "displayname") continue;
                serializedProperty.stringValue = GetDisplayName(Path.GetFileNameWithoutExtension( excel_path));
            }

            configSerialized.ApplyModifiedProperties();

            EditorUtility.SetDirty(config);
            AssetDatabase.SaveAssets();

            error = string.Empty;
            return true;
        }


        private static void SetFieldData(object target,FieldInfo fieldInfo,object value, ExcelFieldsType type)
        {
            if (fieldInfo == null) return;

            // 判断有没有自定义的特性
            CustomExcelFieldImportAndExport importAndExport = fieldInfo.GetCustomAttribute<CustomExcelFieldImportAndExport>();
            if (importAndExport != null) 
            {
                importAndExport.SetValue(target, fieldInfo,value); 
                return;
            }
             
            if (type == ExcelFieldsType.Json)
            {
                object obj = null;
                try
                {
                    // json解析可能会报错 这里处理一下 如果出错就设置为空
                    obj = JsonConvert.DeserializeObject(value.ToString(), fieldInfo.FieldType);
                }
                catch (Exception e)
                {
                    Debug.LogErrorFormat("Json解析失败!内容:{0} 目标类型:{1} 异常原因:{2}", value, fieldInfo.FieldType.FullName, e.ToString());
                }

                fieldInfo.SetValue(target, obj);
            }
            else
            {

                if (type == ExcelFieldsType.ObjectReference)
                {
                    string guid = value.ToString();
                    string asset_path = AssetDatabase.GUIDToAssetPath(guid);
                    if (!string.IsNullOrEmpty(asset_path))
                    {
                        object obj = AssetDatabase.LoadAssetAtPath(asset_path, fieldInfo.FieldType);
                        fieldInfo.SetValue(target, obj);
                    }
                }
                else
                {
                    fieldInfo.SetValue(target, value);
                }

            }
        }


        /// <summary>
        /// 把ScriptableObject集合转成Excel(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="scriptables">ScriptableObject集合</param>
        /// <param name="excel_path">excel路径</param>
        /// <param name="error">报错信息</param>
        /// <returns></returns>
        public static bool ScriptableObjectToExcel<T>(List<T> scriptables,string excel_path,out string error) where T : ScriptableObject
        {  
            if (string.IsNullOrEmpty(excel_path)) 
            {
                error = "excel路径不能为空!";
                Debug.LogError(error);
                return false;
            }

            if (!excel_path.EndsWith(".xlsx")) 
            {
                error = string.Format( "{0} 不是有效的excel文件路径,必须以.xlsx结尾!",excel_path);
                Debug.LogError(error);
                return false;
            }
               

            FileInfo fileInfo = new FileInfo(excel_path);
            using ExcelPackage package = new ExcelPackage(fileInfo);
            if(package.Workbook.Worksheets.Count == 0)
                package.Workbook.Worksheets.Add("Sheet1");

            ExcelWorksheet sheet = package.Workbook.Worksheets[1];

            // 删除excel中的原有数据
            int row = GetExcelRow(sheet); 
            int col = GetExcelCol(sheet);

            for (int i = 1; i <= row; i++) 
            {
                for (int j = 1; j <= col; j++) 
                {
                    sheet.SetValue(i, j, null);
                }
            }

            // 往excel中写数据
            if (scriptables != null) 
            {

                // 解析字段
                Dictionary<int,ExcelFields> fields = ParseFields(scriptables);

                if (fields.Count != 0) 
                {

                    // 往表中写入头
                    foreach (int column in fields.Keys)
                    {
                        sheet.SetValue(1,column, fields[column].name);
                        sheet.SetValue(2, column, fields[column].type.ToString().ToLower());
                        sheet.SetValue(3, column, fields[column].notes);

                        int index = 4;

                        ExcelFields excelField = fields[column];

                        if(column == 1) 
                            sheet.Column(column).Width = 30;

                        foreach (ScriptableObject scriptable in scriptables)
                        {
                            Type type = scriptable.GetType();
                             
                            if (excelField.name == DATA_TYPE)
                            {
                                sheet.SetValue(index, column, type.FullName); 
                            }
                            else 
                            {
                                FieldInfo field = type.GetField(excelField.name, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
                                if (field != null) 
                                { 
                                    object value = ParseFiledData(field.GetValue(scriptable), excelField.type, false,field, scriptable); 
                                    sheet.SetValue(index,column, value); 
                                }
                            }

                            index++;
                        } 
                    } 
                }
            }
             
            try
            {
                package.Save();
            }
            catch (InvalidOperationException)
            { 
                error = string.Format("excel文件:{0}正在被别的应用程序使用,请关闭后重试!", excel_path); 
                Debug.LogError(error);
                return false;
                
            }

            error = string.Empty;
            return true;
        }

        /// <summary>
        /// 把Excel转成List集合(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="sheet">ExcelWorksheet</param>
        /// <param name="values">List集合</param>
        /// <param name="error">报错信息</param>
        /// <returns></returns>
        public static bool ExcelToList<T>(ExcelWorksheet sheet, List<T> values,out string error) where T : new()
        {
            if (values == null)
                values = new List<T>();

            values.Clear();

            int col = GetExcelCol(sheet);

            int row = GetExcelRow(sheet);

            if (row < 3)
            {
                error = "数据错误,小于3行!";
                Debug.LogError(error);
                return false;
            }

            Type type = typeof(T);

            // 行数满足 读取所有的字段  key:列 value:字段 
            Dictionary<int, ExcelFields> fields = ParseFields(sheet, col, string.Empty, string.Empty);

            Dictionary<string, ExcelFields> fields_key_name = new Dictionary<string, ExcelFields>();

            foreach (var item in fields.Values)
            {
                fields_key_name.Add(item.name, item);
            }


            if (fields.Count == 0)
            {
                error = "未查询到字段,请检查配表格式是否正确!";
                Debug.LogWarning(error);
                return false;
            }

            // 所有的数据
            List<Dictionary<string, object>> datas = new List<Dictionary<string, object>>();

            for (int i = 4; i <= row; i++)
            {
                Dictionary<string, object> item = new Dictionary<string, object>();

                foreach (var column in fields.Keys)
                {
                    object obj = sheet.GetValue(i, column);
                    item.Add(fields[column].name, obj);
                }

                datas.Add(item);
            }


            try
            {
                for (int i = 0; i < datas.Count; i++)
                {
                    Dictionary<string, object> item = datas[i];
                     
                    T target = new T();
                     
                    // 把 item 数据 设置给 ScriptableObject 
                    foreach (var fieldName in item.Keys)
                    { 
                        FieldInfo fieldInfo = type.GetField(fieldName, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);

                        object obj = ParseFiledData(item[fieldName], fields_key_name[fieldName].type, true, fieldInfo);

                        SetFieldData(target, fieldInfo, obj, fields_key_name[fieldName].type);
                    } 

                    values.Add(target);
                }
            }
            catch (Exception e)
            {
                Debug.LogException(e);
            }
            finally
            {
                EditorUtility.ClearProgressBar();
            }

            error = string.Empty;
            return true;
        }

        /// <summary>
        /// 把List集合转成Excel(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="values">List集合</param>
        /// <param name="sheet">ExcelWorksheet</param>
        /// <param name="error">报错信息</param>
        /// <returns></returns>
        public static bool ListToExcel<T>(List<T> values, ExcelWorksheet sheet,out string error) 
        {
            Dictionary<int,ExcelFields> fieldInfos = ParseFields<T>();

            if (fieldInfos.Count == 0)
            {
                error = string.Format("未在类型:{0}中查询到可序列化字段!",typeof(T).FullName);
                return false;
            }

            // 清空表  
            int row = GetExcelRow(sheet);
            int col = GetExcelCol(sheet);

            for (int i = 1; i <= row; i++)
            {
                for (int j = 1; j <= col; j++)
                {
                    sheet.SetValue(i, j, null);
                }
            }
             
            // 往表中写入头
            foreach (int column in fieldInfos.Keys)
            {
                sheet.SetValue(1, column, fieldInfos[column].name);
                sheet.SetValue(2, column, fieldInfos[column].type.ToString().ToLower());
                sheet.SetValue(3, column, fieldInfos[column].notes);

                int index = 4;

                ExcelFields excelField = fieldInfos[column];

                if (column == 1)
                    sheet.Column(column).Width = 30;

                foreach (T scriptable in values)
                {
                    Type type = typeof(T);

                    FieldInfo field = type.GetField(excelField.name, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
                    if (field != null) 
                    {                     
                        object value = ParseFiledData(field.GetValue(scriptable), excelField.type, false, field );
                        sheet.SetValue(index, column, value);
                    }
                     
                    index++;
                }
            }
            
             
            error = string.Empty;
            return true;
        }

        /// <summary>
        /// 查询Excel最大的行数(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static int GetExcelRow(ExcelWorksheet sheet) 
        { 
            int row = GetExcelRow(sheet, 1);

            if(row == 0) // 等于0 说明第一列没有数据 尝试从第二列查找 
                row = GetExcelRow(sheet, 2);

            if (row == 0)// 第二列没有数据 尝试从第三列查找 
                row = GetExcelRow(sheet, 3);
             
            return row;
        }

        private static int GetExcelRow(ExcelWorksheet sheet, int col) 
        {
            int row = 0;

            int empty_number = 0;

            for (int i = 1; i <= MAX_ROW; i++)
            {
                object v = sheet.GetValue(i, col);

                if (v != null && !string.IsNullOrEmpty(v.ToString()))
                {
                    row = i;
                    empty_number = 0;
                }
                else
                {
                    empty_number++;
                    // 如果连续30个都为空 则认为后面没有数据了
                    if (empty_number > 30)
                        break;
                }
            }

            return row;
        }

        /// <summary>
        /// 查询Excel最大的列数(仅能在编辑器模式调用,若在运行代码中调用,请添加UNITY_EDITOR宏!)
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static int GetExcelCol(ExcelWorksheet sheet) 
        {
            int col = GetExcelCol(sheet, 1);

            if (col == 0) 
                col = GetExcelCol(sheet, 2);

            if (col == 0)
                col = GetExcelCol(sheet, 3);

            return col;
        }

        private static int GetExcelCol(ExcelWorksheet sheet, int row)
        {
            int col = 0;

            int empty_number = 0;

            for (int i = 1; i <= MAX_COL; i++)
            { 
                object v = sheet.GetValue(row, i);

                if (v != null && !string.IsNullOrEmpty(v.ToString()))
                {
                    col = i;
                    empty_number = 0;
                }
                else
                {
                    empty_number++;
                    // 如果连续30个都为空 则认为后面没有数据了
                    if (empty_number > 30)
                        break;
                }
            }

            return col;
        }


        private static Dictionary<int, ExcelFields> ParseFields(ExcelWorksheet sheet, int col,string excel_path,string exclude_prefix) 
        {  
            // 行数满足 读取所有的字段  key:列 value:字段 
            Dictionary<int, ExcelFields> fields = new Dictionary<int, ExcelFields>();

            for (int i = 1; i <= col; i++)
            {
                // 所有的列
                object name_value = sheet.GetValue(1, i); // 第一行对应的是名称
                if (name_value == null || string.IsNullOrEmpty(name_value.ToString()))
                    continue;
                string name = name_value.ToString();

                // 判断是否需要把这个字段排除掉 
                if ( !string.IsNullOrEmpty(exclude_prefix) && name.StartsWith(exclude_prefix))
                    continue;

                if (IsContainName(fields, name))
                {
                    Debug.LogWarningFormat("配表:{0}中发现重复的字段:{1}",excel_path,name);
                    continue;
                }

                // 解析类型

                object type_value = sheet.GetValue(2, i); // 第二行对应的是类型
                ExcelFieldsType type = ParseFieldType(type_value);
                if (type == ExcelFieldsType.None) {
                    Debug.LogWarningFormat("excel:{0} col:{1} name:{2} 类型解析错误,请设置正确的类型!", excel_path,i,name);
                    continue;
                }

                object note_value = sheet.GetValue(3, i);

                ExcelFields field = new ExcelFields();
                field.name = name;
                field.type = type;
                field.notes = note_value == null ? string.Empty : note_value.ToString();

                fields.Add(i, field);
            }

            return fields;
        }

        private static Dictionary<int, ExcelFields> ParseFields<T>(List<T> scriptables) where T : ScriptableObject
        {
            // 行数满足 读取所有的字段  key:列 value:字段 
            Dictionary<int, ExcelFields> fields = new Dictionary<int, ExcelFields>();

            // 类型
            ExcelFields data_type = new ExcelFields();
            data_type.name = DATA_TYPE;
            data_type.type = ExcelFieldsType.String;
            data_type.notes = "ScriptableObject类型全名称(含命名空间)";

            fields.Add(1, data_type);

            Dictionary<string, ExcelFields> scriptable_object_fields = new Dictionary<string, ExcelFields>(); 

            if (scriptables != null) 
            {
                foreach (ScriptableObject item in scriptables)
                {
                    SerializedObject serializedObject = new SerializedObject(item);
                    List<SerializedProperty> properties = serializedObject.GetAllSerializedProperty();

                    foreach (SerializedProperty property in properties) 
                    {
                        if (scriptable_object_fields.ContainsKey(property.name))
                            continue;

                        Type type = serializedObject.targetObject.GetType();

                        FieldInfo fieldInfo = type.GetField(property.name, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);

                        if (fieldInfo != null && fieldInfo.GetCustomAttribute<ExcelIgnore>() != null) continue;

                        ExcelFields excelFields = new ExcelFields();
                        excelFields.name = property.name;
                        excelFields.type = PropertyTypeToExcelFieldsType(property.propertyType, property.type);
                        excelFields.notes = property.tooltip;

                        scriptable_object_fields.Add(property.name, excelFields);
                    } 
                }
            }


            foreach (var item in scriptable_object_fields.Values)
            {
                if (!item.name.ToLower().Equals("id"))
                    continue; 
                fields.Add(2, item);
                break;
            }
             
            int index = fields.Count;
              
            foreach (var item in scriptable_object_fields.Values)
            {
                // 如果有id上面就会加进去 所这里跳过就行 目的是为了让id在第二位
                if (item.name.ToLower().Equals("id"))
                    continue;

                index++;
                fields.Add(index, item);
            }

            return fields;
        }

        private static Dictionary<int, ExcelFields> ParseFields<T>() 
        {
            Dictionary<int, ExcelFields> fields = new Dictionary<int, ExcelFields>();

            Type type = typeof(T);

            FieldInfo[] fieldInfos = type.GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);

            int index = 1;

            foreach (var item in fieldInfos)
            {
                JsonIgnoreAttribute ignore = item.GetCustomAttribute<JsonIgnoreAttribute>();
                if (ignore != null) continue;

                if (item.IsNotSerialized) continue;

                if (item != null && item.GetCustomAttribute<ExcelIgnore>() != null) continue;

                if (!item.IsPublic && item.GetCustomAttribute<SerializeField>() == null) continue;

                ExcelFields excelFields = new ExcelFields();
                excelFields.name = item.Name;
                excelFields.type = ParseFieldType(item);
                excelFields.notes = string.Empty;
                TooltipAttribute tooltip = item.GetCustomAttribute<TooltipAttribute>();
                if (tooltip != null)
                    excelFields.notes = tooltip.tooltip;

                fields.Add(index, excelFields);
                
                index++;
            }

            return fields;
        }

        private static ExcelFieldsType ParseFieldType(object excel_value) 
        {
            ExcelFieldsType t = ExcelFieldsType.None;

            if (excel_value != null) 
            {
                string type = excel_value.ToString().ToLower();

                if (type.Equals("int"))
                    t = ExcelFieldsType.Int;
                else if (type.Equals("float"))
                    t = ExcelFieldsType.Float;
                else if (type.Equals("double"))
                    t = ExcelFieldsType.Double;
                else if (type.Equals("string"))
                    t = ExcelFieldsType.String;
                else if (type.Equals("bool") || type.Equals("boolean"))
                    t = ExcelFieldsType.Bool;
                else if (type.Equals("long") || type.Equals("int64"))
                    t = ExcelFieldsType.Long;
                else if (type.Equals("json"))
                    t = ExcelFieldsType.Json;
                else if (type.Equals("objectreference"))
                    t = ExcelFieldsType.ObjectReference;
                else if (type.Equals("enum"))
                    t = ExcelFieldsType.Enum;
            }

            return t;
        }

        private static ExcelFieldsType ParseFieldType(FieldInfo fieldInfo) 
        {
            ExcelFieldsType t = ExcelFieldsType.None;
             
            if (fieldInfo != null)
            { 
                Type type = fieldInfo.FieldType;

                if (type == typeof(int) || type == typeof(uint) || type == typeof(short) || type == typeof(ushort))
                    t = ExcelFieldsType.Int;
                else if (type == typeof(float))
                    t = ExcelFieldsType.Float;
                else if (type == typeof(double))
                    t = ExcelFieldsType.Double;
                else if (type == typeof(string))
                    t = ExcelFieldsType.String;
                else if (type == typeof(bool))
                    t = ExcelFieldsType.Bool;
                else if (type == typeof(long))
                    t = ExcelFieldsType.Long;
                else if (type.IsEnum)
                    t = ExcelFieldsType.Enum;
                else if (type.IsSubclassOf(typeof(UnityEngine.Object)))
                    t = ExcelFieldsType.ObjectReference;
                else
                    t = ExcelFieldsType.Json;
            }
             
            return t;

        }

        /// <summary>
        /// 解析或者处理数据 
        /// from_excel 为 true 时 obj 是从 excel中读到的内容 返回值是要写入ScriptableObject
        /// from_excel 为 false 时 obj 是 ScriptableObject的字段的值 返回值是要写入到Excel中的内容
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="type"></param>
        /// <param name="from_excel"></param>
        /// <returns></returns>
        private static object ParseFiledData(object obj,ExcelFieldsType type,bool from_excel = true,FieldInfo fieldInfo = null,ScriptableObject scriptableObject = null)
        {
            if (fieldInfo != null) 
            {

                CustomExcelFieldImportAndExport importAndExport = fieldInfo.GetCustomAttribute<CustomExcelFieldImportAndExport>();
                if (importAndExport != null)
                {
                    if (from_excel)
                    {
                        return importAndExport.Import(scriptableObject,obj, type);
                    }
                    else {
                        return importAndExport.Export(scriptableObject, obj, type);
                    }
                }
            }


            switch (type)
            { 
                case ExcelFieldsType.Int:
                    int result_int = 0;
                    if (obj != null)
                        int.TryParse(obj.ToString(), out result_int);
                    return result_int; 
                case ExcelFieldsType.Float:

                    float result_float = 0;
                    if (obj != null)
                        float.TryParse(obj.ToString(), out result_float);
                    return result_float; 
                case ExcelFieldsType.Double:

                    double result_double = 0;

                    if (obj != null)
                        double.TryParse(obj.ToString(), out result_double);

                    return result_double;

                //string 一样处理 
                case ExcelFieldsType.String:
                    return obj == null ? string.Empty : obj.ToString();
                case ExcelFieldsType.Bool: 
                    bool result_bool = false; 
                    if (obj != null)
                        bool.TryParse(obj.ToString(), out result_bool); 
                    return result_bool;
                case ExcelFieldsType.Long:
                    long result_long = 0;

                    if (obj != null)
                        long.TryParse(obj.ToString(), out result_long);

                    return result_long;

                case ExcelFieldsType.Json:
                    if (from_excel)
                        return obj != null ? obj.ToString() : string.Empty;

                    string json = string.Empty;

                    try
                    {
                        // json序列化可能会报错 所以来这里处理一下，如果出错就返回空
                        json = JsonConvert.SerializeObject(obj, Formatting.Indented);
                    }
                    catch (Exception e)
                    {
                        Debug.LogErrorFormat("Json序列化失败:{0}",e.ToString());
                    }

                    return json;

                case ExcelFieldsType.ObjectReference:

                    if (!from_excel) 
                    {                  
                        UnityEngine.Object unityObj = obj as UnityEngine.Object;
                        string asset_path = AssetDatabase.GetAssetPath(unityObj);
                        return AssetDatabase.AssetPathToGUID(asset_path);
                    }

                    return obj != null ? obj.ToString() : string.Empty;

                case ExcelFieldsType.Enum:

                    int v = 0;

                    if (from_excel)
                    {
                        if (obj != null) 
                        {
                            if (int.TryParse(obj.ToString(), out v))
                            {
                                return v;
                            }
                            else 
                            {
                                try
                                {
                                    return Enum.Parse(fieldInfo.FieldType, obj.ToString(), true);
                                }
                                catch (Exception )
                                { 
                                }

                                
                            }
                        }
                        return v;
                    }

                     
                    return obj.ToString();
                    

            }

            return string.Empty;
        }
         
        private static bool IsContainName(Dictionary<int, ExcelFields> fields, string name) {

            foreach (var field in fields.Values)
            {
                if(field.name.Equals(name))
                    return true;
            }

            return false;
        }
         
        private static ExcelFieldsType PropertyTypeToExcelFieldsType(SerializedPropertyType propertyType,string type) 
        {
            //Debug.LogFormat("property_type:{0} type:{1}",propertyType,type);

            switch (propertyType)
            { 
                case SerializedPropertyType.Integer: 
                    if (type == "ulong" || type == "long")
                        return ExcelFieldsType.Long;
                    return ExcelFieldsType.Int;
                case SerializedPropertyType.Boolean:
                    return ExcelFieldsType.Bool;
                case SerializedPropertyType.Float:
                    if(type == "double")
                        return ExcelFieldsType.Double; 
                    return ExcelFieldsType.Float;
                case SerializedPropertyType.String:
                    return ExcelFieldsType.String; 
                case SerializedPropertyType.ObjectReference: 
                    return ExcelFieldsType.ObjectReference;

                case SerializedPropertyType.Enum:
                    return ExcelFieldsType.Enum;
            }

            return ExcelFieldsType.Json;
        }


        private static string GetDisplayName(string name)
        {
            if (name.EndsWith(")") && name.Contains("("))
            {
                for (int i = name.Length - 1; i >= 0; i--)
                {
                    if (name[i] == '(')
                    {
                        return name.Substring(i + 1, name.Length - i - 2);
                    }
                }
            }

            return string.Empty;
        }


    }
}

